package dbadapters;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import commands.AddStudent;
import containers.NewLoanContainer;
import containers.StudentContainer;

import entities.CoSigner;
import entities.FinancialInfo;
import entities.Loan;
import entities.PaymentPlan;
import entities.Student;

/**
 * adapter for feeding all students and student information that is in the
 * database into the system on startup
 * 
 * @author gke703
 */
public class LoadStudentsDB {

	int id;

	/**
	 * loads all of the student, financial info and loans into system from the
	 * db
	 * 
	 * @precond db does not have duplicate students
	 */
	public LoadStudentsDB() {

		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException cnfe) {
			System.out.println("Couldn't find driver class:");
			cnfe.printStackTrace();
		}

		Connection c = null;
		try {
			c = DriverManager.getConnection(
					"jdbc:postgresql://papyrus.usask.ca/cmpt370group00",
					"cmpt370group00", "an7x482d");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println(e);
		}

		Statement stmt = null;
		try {
			stmt = c.createStatement();
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}

		// load all student info
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery("SELECT * FROM students");
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}
		try {
			while (rs.next()) {

				System.out.println(rs.getArray(1).toString());
				// make a new student for each row in db and add it to system
				Student myStudent = new Student(Integer.parseInt(rs.getArray(1)
						.toString()), rs.getArray(2).toString());
				id = myStudent.getID();
				myStudent.setName(rs.getArray(3).toString());
				myStudent.setBirthdate(rs.getArray(4).toString());
				myStudent.setAddress(rs.getArray(5).toString());
				myStudent.setPhoneNumber(rs.getArray(6).toString());
				myStudent.setEmail(rs.getArray(7).toString());
				myStudent.setInstitution(rs.getArray(8).toString());
				myStudent.setCitizenship(Boolean.parseBoolean(rs.getArray(9)
						.toString()));

				// parse enumerated type maritalStatuses
				Student.maritalStatuses studentMarital = Student.maritalStatuses.SINGLE;
				if (rs.getArray(10).toString().equals("SINGLE")) {
					studentMarital = Student.maritalStatuses.SINGLE;
				} else if (rs.getArray(10).toString().equals("MARRIED")) {
					studentMarital = Student.maritalStatuses.MARRIED;
				} else if (rs.getArray(10).toString().equals("COMMONLAW")) {
					studentMarital = Student.maritalStatuses.COMMONLAW;
				}
				myStudent.setMaritalStatus(studentMarital);

				// do a similar process for mother/father education levels
				Student.education studentFather = Student.education.NONE;
				if (rs.getArray(11).toString().equals("DEGREE")) {
					studentFather = Student.education.DEGREE;
				} else if (rs.getArray(11).toString().equals("DIPLOMA")) {
					studentFather = Student.education.DIPLOMA;
				} else if (rs.getArray(11).toString().equals("HIGH_SCHOOL")) {
					studentFather = Student.education.HIGH_SCHOOL;
				}
				myStudent.setFatherEducation(studentFather);

				Student.education studentMother = Student.education.NONE;
				if (rs.getArray(12).toString().equals("DEGREE")) {
					studentMother = Student.education.DEGREE;
				} else if (rs.getArray(12).toString().equals("DIPLOMA")) {
					studentMother = Student.education.DIPLOMA;
				} else if (rs.getArray(12).toString().equals("HIGH_SCHOOL")) {
					studentMother = Student.education.HIGH_SCHOOL;
				}
				myStudent.setMotherEducation(studentMother);

				myStudent.setSIN(rs.getArray(13).toString());

				myStudent.setPaymentPlanCreated(Boolean.parseBoolean(rs
						.getArray(14).toString()));

				new AddStudent(myStudent);
			}
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}

		// load all student payment plan information
		rs = null;
		try {
			rs = stmt.executeQuery("SELECT * FROM payment_plan");
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}
		try {
			while (rs.next()) {
				Student myStudent = null;
				myStudent = StudentContainer.dictionary().get(
						Integer.parseInt(rs.getArray(1).toString()));
				if (myStudent != null) {
					PaymentPlan myP = new PaymentPlan(Float.parseFloat(rs
							.getArray(2).toString()));
					myP.setAmountReceived(Float.parseFloat(rs.getArray(4)
							.toString()));
					myP.setNextAmount(Double.parseDouble(rs.getArray(5)
							.toString()));
					myP.setPrevAmount(Double.parseDouble(rs.getArray(6)
							.toString()));
					myP.setNextDateAsString(rs.getArray(7).toString());
					// myP.setPrevDateAsString(rs.getArray(8).toString());

					myStudent.setPaymentPlan(myP);
				}
			}
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}

		// load all student financial info
		rs = null;
		try {
			rs = stmt.executeQuery("SELECT * FROM students_financialinfo");
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}
		try {
			while (rs.next()) {
				Student myStudent = StudentContainer.dictionary().get(
						Integer.parseInt(rs.getArray(1).toString()));
				FinancialInfo myF = new FinancialInfo();

				myF.setPersonalIncome(Float.parseFloat(rs.getArray(2)
						.toString()));
				myF.setHouseholdIncome(Float.parseFloat(rs.getArray(3)
						.toString()));
				myF.setTaxableEarnings(Float.parseFloat(rs.getArray(4)
						.toString()));
				myF.setChildSupport(Float.parseFloat(rs.getArray(5).toString()));
				myF.setScholarship(Float.parseFloat(rs.getArray(6).toString()));
				myF.setMonthlyExpenses(Float.parseFloat(rs.getArray(7)
						.toString()));

				myStudent.setFinances(myF);
			}
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}

		// load all student loan info
		rs = null;
		try {
			rs = stmt.executeQuery("SELECT * FROM students_loans");
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}
		try {
			while (rs.next()) {
				Student myStudent = StudentContainer.dictionary().get(
						Integer.parseInt(rs.getArray(1).toString()));
				Loan myLoan = new Loan(myStudent);

				myLoan.setAmountRequested(Float.parseFloat(rs.getArray(2)
						.toString()));
				myLoan.setAmountGiven(Float.parseFloat(rs.getArray(3)
						.toString()));
				myLoan.setIsNew(Boolean.parseBoolean((rs.getArray(4).toString())));
				myLoan.setApproval((Boolean.parseBoolean((rs.getArray(5)
						.toString()))));

				myStudent.setLoan(myLoan);
				if (myLoan.isNew()) {
					NewLoanContainer.dictionary().put(myStudent.getID(),
							myStudent);
				}
			}
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}

		// load all cosigner info
		rs = null;
		try {
			rs = stmt.executeQuery("SELECT * FROM cosigners");
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}
		try {
			while (rs.next()) {

				CoSigner myCosigner = new CoSigner(Integer.parseInt(rs
						.getArray(1).toString()));
				Student myStudent = StudentContainer.dictionary().get(
						Integer.parseInt(rs.getArray(2).toString()));
				myStudent.getLoan().setCosigner(myCosigner);
				myCosigner.setCosignedLoan(myStudent.getLoan());

				myCosigner.setName(rs.getArray(3).toString());
				myCosigner.setBirthdate(rs.getArray(4).toString());
				myCosigner.setAddress(rs.getArray(5).toString());
				myCosigner.setPhoneNumber(rs.getArray(6).toString());
				myCosigner.setEmail(rs.getArray(7).toString());

				FinancialInfo myF = new FinancialInfo();
				myCosigner.setFinancialInfo(myF);

				myF.setPersonalIncome(Float.parseFloat(rs.getArray(8)
						.toString()));
				myF.setHouseholdIncome(Float.parseFloat(rs.getArray(9)
						.toString()));
				myF.setTaxableEarnings(Float.parseFloat(rs.getArray(10)
						.toString()));
				myF.setChildSupport(Float
						.parseFloat(rs.getArray(11).toString()));
				myF.setScholarship(Float.parseFloat(rs.getArray(12).toString()));
				myF.setMonthlyExpenses(Float.parseFloat(rs.getArray(13)
						.toString()));

			}
		} catch (SQLException e3) {
			// TODO Auto-generated catch block
			e3.printStackTrace();
		}

		// Cleanup
		try {
			rs.close();
		} catch (SQLException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}

		try {
			stmt.close();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		try {
			c.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * test to see if loading works
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		new LoadStudentsDB();

	}

}
